﻿//
// (C) Copyright 2009 Irantha Suwandarathna (irantha@gmail.com)
// All rights reserved.
//

using System;
using System.Text;
using System.Collections.Generic;
using System.Linq;
using System.IO;
using System.Data;
using System.Data.Common;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using EffiProzDB;
using EffiProzDB.Lib;
using EffiProzDB.DataTypes;
using System.Data.EffiProzDB;



namespace EffiProzDBTest
{
    /// <summary>
    /// Summary description for DataTypeTest
    /// </summary>
    [TestClass]
    public class DataTypeTest
    {

        SessionInterface sessionProxy;
        String user = "sa";
        String password = "";
        String connType = DatabaseURL.S_MEM;
       // String host = "localhost";
       // int port = 0;
       // String path = "TestDT";
        String database = "TestDT";
       // string sql = "";
        HsqlProperties props = new HsqlProperties();
        string dataPath = "../../../EffiProzDBTest/Data/";


        public void CleanUp(string prefix)
        {
            string[] files = Directory.GetFiles(dataPath, prefix + "*");
            foreach (var file in files)
                File.Delete(file);
        }


        public DataTypeTest()
        {
            
            sessionProxy = DatabaseManager.newSession(connType, database,
                     user, password, props);      
        }

        private TestContext testContextInstance;

        /// <summary>
        ///Gets or sets the test context which provides
        ///information about and functionality for the current test run.
        ///</summary>
        public TestContext TestContext
        {
            get
            {
                return testContextInstance;
            }
            set
            {
                testContextInstance = value;
            }
        }

        #region Additional test attributes
        //
        // You can use the following additional attributes as you write your tests:
        //
        // Use ClassInitialize to run code before running the first test in the class
        // [ClassInitialize()]
        // public static void MyClassInitialize(TestContext testContext) { }
        //
        // Use ClassCleanup to run code after all tests in a class have run
        // [ClassCleanup()]
        // public static void MyClassCleanup() { }
        //
        // Use TestInitialize to run code before running each test 
        // [TestInitialize()]
        // public void MyTestInitialize() { }
        //
        // Use TestCleanup to run code after each test has run
        // [TestCleanup()]
        // public void MyTestCleanup() { }
        //
        #endregion

        [TestMethod]
        public void TestBool()
        {

            Result resultIn;

            string table = TestHelper.GetNewTableName();
            string sql = "CREATE TABLE " + table + " (col1 int , col2 BIT, col3 BOOLEAN);"; // not allow null for col1

            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);


            int col1val = 0;
            bool col2val = false;
            bool col3val = true;
            sql = TestHelper.CreateStdInsert(table, 3, col1val, col2val, col3val);

            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            sql = TestHelper.CreateStdSelect(table, 3);
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 1);

            TestHelper.MatchSingleResult(resultIn, col1val, col2val, col3val);

        }

        [TestMethod]
        public void TestDateTime()
        {

            Result resultIn;

            string table = TestHelper.GetNewTableName();
            string sql = "CREATE TABLE " + table + " (col1 int , col2 DATETIME);"; // not allow null for col1

            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);


            int col1val = 0;
            DateTime col2val = DateTime.Now;

            sql = TestHelper.CreateStdInsert(table, 2, col1val, col2val);

            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            sql = TestHelper.CreateStdSelect(table, 2);
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 1);

            TestHelper.MatchSingleResult(resultIn, col1val, col2val);

        }

        [TestMethod]
        public void IgnoreCaseDataType()
        {
            Result resultIn;

            string table = TestHelper.GetNewTableName();
            string sql = "CREATE TABLE " + table + " (col1 varchar(32) , col2 VARCHAR_IGNORECASE(32)); "; // not allow null for col1

            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            string col1val = "IranTHa";
            string col2val = "IranTHa";

            sql = TestHelper.CreateStdInsert(table, 2, col1val, col2val);
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            sql = "SELECT col1, col2 FROM " + table + " WHERE col1='" + col1val.ToLower() + "' ;";
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 0);


            sql = "SELECT col1, col2 FROM " + table + " WHERE col2='" + col2val.ToLower() + "' ;";
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 1);
            TestHelper.MatchSingleResultRow(resultIn, 0, col1val, col2val);

        }
        
        [TestMethod]
        public void ArithMaticExpressions()
        {
            Result resultIn;

            byte val1 = 3;
            short val2 = 34;
            int val3 = 4595;
            long val4 = 78993L;
            double val5 = 789.65D;
            decimal val6 = 890.78M;
            

            string table = TestHelper.GetNewTableName();
            string sql = "CREATE TABLE " + table + " (col1 TINYINT , col2 SMALLINT, col3 INT, col4 BIGINT,col5 DOUBLE,col6 DECIMAL )";
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            sql = TestHelper.CreateStdInsert(table, 6, val1, val2, val3,val4,val5, val6);
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

           //retrieval check
            sql = "SELECT col1, col2, col3, col4, col5, col6 FROM " + table + " ;";
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 1);
            TestHelper.MatchSingleResultRow2(resultIn, 0, (int)val1, (int)val2, val3, val4, val5, val6);

            //negation check
            sql = "SELECT -col1, -col2, -col3, -col4, -col5, -col6 FROM " + table + " ;";
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 1);
            TestHelper.MatchSingleResultRow2(resultIn, 0, -(int)val1, -(int)val2,-val3, -val4, -val5, -val6);

          
            int rval1 = 4555;    
            //substract
            sql = "SELECT " + rval1 + "-col1, " + rval1 + "-col2, " + rval1 + " -col3, " + rval1 + " -col4, " + rval1 + " -col5, " + rval1 + " -col6 FROM " + table + " ;";
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 1);
            TestHelper.MatchSingleResultRow2(resultIn, 0, rval1 - val1, rval1 - val2, rval1 - val3, rval1 - val4, rval1 - val5, rval1 - val6);

            //multiply
            sql = "SELECT " + rval1 + "*col1, " + rval1 + "*col2, " + rval1 + " *col3, " + rval1 + " *col4, " + rval1 + " *col5, " + rval1 + " *col6 FROM " + table + " ;";
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 1);
            TestHelper.MatchSingleResultRow2(resultIn, 0, rval1 * val1, rval1 * val2, rval1 * val3, rval1 * val4, rval1 * val5, rval1 * val6);

            //divide
            sql = "SELECT " + rval1 + "/col1, " + rval1 + "/col2, " + rval1 + "/col3, " + rval1 + " /col4, " + rval1 + " /col5, " + rval1 + " /col6 FROM " + table + " ;";
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 1);
            TestHelper.MatchSingleResultRow2(resultIn, 0, rval1 / val1, rval1 / val2, rval1 / val3, rval1 / val4, rval1 / val5, rval1 / val6);

        }

        [TestMethod]
        public void BinaryDataType()
        {
            CleanUp("BinaryDT");
            SessionInterface sessionProxy = DatabaseManager.newSession(DatabaseURL.S_FILE, dataPath + "BinaryDT",
                     user, password, props);    

            Result resultIn;

            string table = TestHelper.GetNewTableName();
            string sql = "CREATE CACHED  TABLE " + table + " (col1 INT , col2 BINARY); "; 

            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            int col1val = 1;
            byte[] col2val = Encoding.Default.GetBytes("Hello Binary Data!!!");
            object[] parameters = { col1val, new Binary( col2val ,true)};

            sql = "INSERT INTO "+table+"(col1,col2) VALUES(?,?)";

            resultIn = TestHelper.ExecutePrepareStatement(sessionProxy,sql,parameters);

            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, "SHUTDOWN ;");
            sessionProxy = DatabaseManager.newSession(DatabaseURL.S_FILE, dataPath + "BinaryDT",
                     user, password, props); 

            sql = TestHelper.CreateStdSelect(table, 2);
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 1);

            Iterator it = resultIn.iterator();
            object[] row = (object[])it.next();

            Assert.AreEqual(col1val, (int)row[0]);
            Binary outBinary = (Binary)row[1];
            string bval = Encoding.Default.GetString(outBinary.getBytes());
            Assert.AreEqual("Hello Binary Data!!!", bval);        

        }

        [Serializable]
        class TestClass
        {
            public string Name = "irantha";
            public string Car = "Corolla";
        }

        [TestMethod]
        public void DotNetObjectDataType()
        {
            CleanUp("DotNetObjectDT");
            SessionInterface sessionProxy = DatabaseManager.newSession(DatabaseURL.S_FILE, dataPath + "DotNetObjectDT",
                     user, password, props);

            Result resultIn;

            string table = TestHelper.GetNewTableName();
            string sql = "CREATE CACHED  TABLE " + table + " (col1 INT , col2 OTHER); ";

            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            int col1val = 1;
            TestClass col2val = new TestClass();
            object[] parameters = { col1val, new DotNetObject(col2val) };

            sql = "INSERT INTO " + table + "(col1,col2) VALUES(?,?)";

            resultIn = TestHelper.ExecutePrepareStatement(sessionProxy, sql, parameters);

            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, "SHUTDOWN ;");
            sessionProxy = DatabaseManager.newSession(DatabaseURL.S_FILE, dataPath + "DotNetObjectDT",
                     user, password, props);

            sql = TestHelper.CreateStdSelect(table, 2);
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 1);

            Iterator it = resultIn.iterator();
            object[] row = (object[])it.next();

            Assert.AreEqual(col1val, (int)row[0]);
            DotNetObject outObj = (DotNetObject)row[1];
            TestClass cval = (TestClass)outObj.getObject();
            Assert.AreEqual(col2val.Name, cval.Name);
            Assert.AreEqual(col2val.Car, cval.Car);

        }

        [TestMethod]
        public void QuoteTest()
        {
            CleanUp("StoredProcedures");
            SessionInterface sessionProxy = DatabaseManager.newSession(DatabaseURL.S_FILE, dataPath + "StoredProcedures",
                     user, password, props);

            Result resultIn;

            string table = TestHelper.GetNewTableName();
            string sql = "CREATE CACHED  TABLE " + table + " (col1 INT , col2 VARCHAR); ";
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);


            int col1val = 8;
            string col2val = "Irantha''s dog''s house";
            sql = TestHelper.CreateStdInsert(table,2, col1val, col2val);
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            sql = TestHelper.CreateStdSelect(table, 2);
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 1);
            col2val = "Irantha's dog's house";
            TestHelper.MatchSingleResultRow(resultIn, 0, col1val, col2val);
        }

        [TestMethod]
        public void EscapeTest()
        {
            string connString = "Type=Memory ; Data Source=testescape; User=sa; Password=;";
            string sql = "CREATE TABLE TEST(ID INT, NAME VARCHAR);";
            using (EffiProzDBConnection conn = new EffiProzDBConnection(connString))
            {
                DbCommand command = conn.CreateCommand();
                command.CommandText = sql;

                conn.Open();
                int count = command.ExecuteNonQuery();
                command.CommandText = "INSERT INTO TEST(ID , NAME ) VALUES(1,'TEST%TEST'); INSERT INTO TEST(ID , NAME ) VALUES(NULL,'TESTG%TGG');";
                count = command.ExecuteNonQuery();
                Assert.AreEqual(count, 1);

                command.CommandText = "SELECT count(*) FROM TEST WHERE NAME LIKE 'TEST%T%';";
                count =(int) command.ExecuteScalar();              
                Assert.AreEqual(2, count);

                command.CommandText = "SELECT count(*) FROM TEST WHERE NAME LIKE 'TEST\\%T%' ESCAPE'\\';";
                count = (int)command.ExecuteScalar();
                Assert.AreEqual(1, count);
               
            }
        }


    }
}
